
package com.rt.schedulenew.utils.util;
import org.springframework.web.multipart.MultipartFile;
import java.text.DateFormat;
import org.apache.poi.ss.usermodel.CellType;
import java.text.SimpleDateFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import java.text.DecimalFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.io.File;
public class ExcelPoiImportUtils
{
    public static void main(String[] args) {
        String[] strings = "检查项目字典.xlsx".split("\\.");
        File file = new File("F:\\Users\\long\\Downloads\\检查项目字典.xlsx");
        List<List<String>> excelListList = importFromExcel(file);
        for (int i = 0; i < excelListList.size(); ++i) {
            System.out.println(excelListList.get(i));
        }
    }
    
    public static List<List<String>> importFromExcel(File file) {
        List<List<String>> excelListList = new ArrayList<List<String>>();
        Workbook workBook = null;
        String fileName = file.getName();
        try {
            if (fileName.endsWith("xls")) {
                workBook = new HSSFWorkbook(new FileInputStream(file));
            }
            else if (fileName.endsWith("xlsx")) {
                workBook = new XSSFWorkbook(new FileInputStream(file));
            }
        }
        catch (IOException e) {
            e.printStackTrace();
        }
        Sheet sheet = workBook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        int cellNums = 0;
        for (int i = 0; i < rows; ++i) {
            Row row = sheet.getRow(i);
            if (i == 0) {
                cellNums = row.getPhysicalNumberOfCells();
            }
            else {
                List<String> excelList = new ArrayList<String>();
                for (int j = 0; j < cellNums; ++j) {
                    Cell cell = row.getCell(j);
                    String cellValue = getCellValue(cell).trim();
                    excelList.add(cellValue);
                }
                excelListList.add(excelList);
            }
        }
        return excelListList;
    }
    
    public static String getCellValue(Cell cell) {
        String cellValue = null;
        if (cell == null) {
            return cellValue = "";
        }
        CellType cellType = cell.getCellType();
        DecimalFormat decimalFormat = new DecimalFormat("0");
        String name = cellType.name();
        switch (name) {
            case "BLANK": {
                cellValue = "";
                break;
            }
            case "NUMERIC": {
                if (DateUtil.isCellDateFormatted(cell)) {
                    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = dateFormat.format(cell.getDateCellValue());
                    break;
                }
                String number = String.valueOf(cell.getNumericCellValue());
                if (number.indexOf(".") != -1) {
                    decimalFormat = new DecimalFormat("#.###");
                }
                cellValue = decimalFormat.format(cell.getNumericCellValue());
                break;
            }
            case "STRING": {
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            }
            case "FORMULA": {
                cell.setCellType(CellType.NUMERIC);
                cellValue = decimalFormat.format(cell.getNumericCellValue());
                break;
            }
            case "BOOLEAN": {
                cellValue = String.valueOf(cell.getBooleanCellValue());
            }
            case "ERROR": {
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            }
            default: {
                cellValue = cell.getStringCellValue();
                break;
            }
        }
        return cellValue;
    }
    
    public static File transferToFile(MultipartFile multipartFile) {
        File file = null;
        try {
            String originalFilename = multipartFile.getOriginalFilename();
            String[] filename = originalFilename.split("\\.");
            file = File.createTempFile(filename[0], filename[1]);
            multipartFile.transferTo(file);
            file.deleteOnExit();
        }
        catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }
}
